package com.general.common.action;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

import org.apache.commons.io.FilenameUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.general.utils.base.Constant;

/**
 * 读取Excel
 * @author qilm
 *
 */
public class FileImportAction extends BaseAction {
	private static final long serialVersionUID = 1L;

    /** 
     * 分隔符 
     */  
	private final static String SEPARATOR = "|";  
	
    /** 
     * 对外提供读取excel 的方法 
     * */  
	public List<List<Object>> readExcel(File file) throws Exception{  
	   String fileName = file.getName();  
	   String extension = fileName.lastIndexOf(".")==-1?"":fileName.substring(fileName.lastIndexOf(".")+1);  
	   if(Constant.XLS.equals(extension)){  
	    return read2003Excel(file);  
	   }else if(Constant.XLSX.equals(extension)){  
	    return read2007Excel(file);  
	   }else{  
	    throw new IOException("不支持的文件类型");
	   }  
	}  

	/**
	 * 对外提供读取excel 的方法
	 * 
	 * @param fileInputStream
	 *            数据流
	 * @param 文件类型
	 *            （xls、xlsx）
	 * */
	public List<List<Object>> readExcel(FileInputStream fileInputStream,
			String fileContentType) throws Exception {
		if (Constant.XLS.equals(fileContentType)) {
			HSSFWorkbook hwb = new HSSFWorkbook(fileInputStream);  
			HSSFSheet sheet = hwb.getSheetAt(0);  
			return read2003Excel(sheet);
		} else if (Constant.XLSX.equals(fileContentType)) {
			XSSFWorkbook xwb = new XSSFWorkbook(fileInputStream);  
			// 读取第一章表格内容  
			XSSFSheet sheet = xwb.getSheetAt(0); 
			return read2007Excel(sheet);
		} else {
			throw new IOException("不支持的文件类型");
		}
	}

	/** 
	* 读取 office 2003 excel 
	 * @throws IOException */  
	private List<List<Object>> read2003Excel(File file) throws IOException{  
		FileInputStream fileInputStream = new FileInputStream(file);
		HSSFWorkbook hwb = new HSSFWorkbook(fileInputStream);  
		HSSFSheet sheet = hwb.getSheetAt(0);		
		return read2003Excel(sheet);
	}
	
	/** 
	* 读取 office 2003 excel 
	* @throws IOException  
	* @throws FileNotFoundException */  
	public List<List<Object>> read2003Excel(HSSFSheet sheet) throws IOException{  
	   List<List<Object>> list = new LinkedList<List<Object>>();  
//	   HSSFWorkbook hwb = new HSSFWorkbook(fileInputStream);  
//	   HSSFSheet sheet = hwb.getSheetAt(0);  
	   Object value = null;  
	   HSSFRow row = null;  
	   HSSFCell cell = null;   
	   for(int i = sheet.getFirstRowNum();i<= sheet.getPhysicalNumberOfRows();i++){  
	    row = sheet.getRow(i);  
	    if (row == null) { 
	     continue;  
	    }  
	    List<Object> linked = new LinkedList<Object>();  
	    for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {  
	     cell = row.getCell(j);  
	     if (cell == null) {  
	      continue;  
	     }  
	     DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符  
	     SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串  
	     DecimalFormat nf = new DecimalFormat("0");// 格式化数字  
	     switch (cell.getCellType()) {  
	     case XSSFCell.CELL_TYPE_STRING:  
	      System.out.println(i+"行"+j+" 列 is String type");  
	      value = cell.getStringCellValue();  
	      break;  
	     case XSSFCell.CELL_TYPE_NUMERIC:  
	      System.out.println(i+"行"+j+" 列 is Number type ; DateFormt:"+cell.getCellStyle().getDataFormatString());  
	      if("@".equals(cell.getCellStyle().getDataFormatString())){  
	         value = df.format(cell.getNumericCellValue());  
	      } else if("General".equals(cell.getCellStyle().getDataFormatString())){  
	         value = nf.format(cell.getNumericCellValue());  
	      }else{  
	        value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));  
	      }  
	      break;  
	     case XSSFCell.CELL_TYPE_BOOLEAN:  
	      System.out.println(i+"行"+j+" 列 is Boolean type");  
	      value = cell.getBooleanCellValue();  
	      break;  
	     case XSSFCell.CELL_TYPE_BLANK:  
	      System.out.println(i+"行"+j+" 列 is Blank type");  
	      value = "";  
	      break;  
	     default:  
	      System.out.println(i+"行"+j+" 列 is default type");  
	      value = cell.toString();  
	     }  
	     if (value == null || "".equals(value)) {  
	      continue;  
	     }  
	     linked.add(value);    
	   }  
	    list.add(linked);  
	   }  
	   return list;  
	}  

	/** 
	* 读取Office 2007 excel 
	* */  
	private List<List<Object>> read2007Excel(File file) throws Exception { 
		FileInputStream fileInputStream = new FileInputStream(file);
		XSSFWorkbook xwb = new XSSFWorkbook(fileInputStream);  
		// 读取第一章表格内容  
		XSSFSheet sheet = xwb.getSheetAt(0); 
		return read2007Excel(sheet);
		
	}
	/** 
	* 读取Office 2007 excel 
	 * @throws InvalidFormatException 
	* */  
	public List<List<Object>> read2007Excel(XSSFSheet sheet) throws Exception{  
	   List<List<Object>> list = new LinkedList<List<Object>>();
	   //OPCPackage opcPackage = OPCPackage.open((InputStream)fileInputStream);
	   //XSSFWorkbook xwb = new XSSFWorkbook(opcPackage);
	   
	   // 构造 XSSFWorkbook 对象，strPath 传入文件路径  
	   //XSSFWorkbook xwb = new XSSFWorkbook(fileInputStream);  
	   
	   // 读取第一章表格内容  
	   //XSSFSheet sheet = xwb.getSheetAt(0); 
	   Object value = null;  
	   XSSFRow row = null;  
	   XSSFCell cell = null;  
	   for (int i = sheet.getFirstRowNum(); i <= sheet  
	     .getPhysicalNumberOfRows(); i++) {  
	    row = sheet.getRow(i);  
	    if (row == null) {  
	     continue;  
	    }  
	    List<Object> linked = new LinkedList<Object>();  
	    for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {  
	     cell = row.getCell(j);  
	     if (cell == null) {  
	      continue;  
	     }  
	     DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符  
	     SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串  
	     DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字  
	     switch (cell.getCellType()) {  
	     case XSSFCell.CELL_TYPE_STRING:  
	      System.out.println(i+"行"+j+" 列 is String type");  
	      value = cell.getStringCellValue();  
	      break;  
	     case XSSFCell.CELL_TYPE_NUMERIC:  
	      System.out.println(i+"行"+j+" 列 is Number type ; DateFormt:"+cell.getCellStyle().getDataFormatString());  
	      if("@".equals(cell.getCellStyle().getDataFormatString())){  
	        value = df.format(cell.getNumericCellValue());  
	      } else if("General".equals(cell.getCellStyle().getDataFormatString())){  
	        value = nf.format(cell.getNumericCellValue());  
	      }else{  
	       value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));  
	      }  
	      break;  
	     case XSSFCell.CELL_TYPE_BOOLEAN:  
	      System.out.println(i+"行"+j+" 列 is Boolean type");  
	      value = cell.getBooleanCellValue();  
	      break;  
	     case XSSFCell.CELL_TYPE_BLANK:  
	      System.out.println(i+"行"+j+" 列 is Blank type");  
	      value = "";  
	      break;  
	     default:  
	      System.out.println(i+"行"+j+" 列 is default type");  
	      value = cell.toString();  
	     }  
	     if (value == null || "".equals(value)) {  
	      continue;  
	     }  
	     linked.add(value);  
	    }  
	    list.add(linked);  
	   }  
	   return list;  
	}  

	//=========================================我是分割线===============================================
    /** 
     * 由Excel文件的Sheet导出至List 
     *  
     * @param file 
     * @param sheetNum 
     * @return 
     */  
    public static List<String> exportListFromExcel(File file, int sheetNum)  
            throws IOException {  
        return exportListFromExcel(new FileInputStream(file),  
                FilenameUtils.getExtension(file.getName()), sheetNum);  
    }  
    
    /** 
     * 由Excel流的Sheet导出至List 
     *  
     * @param is 
     * @param extensionName 
     * @param sheetNum 
     * @return 
     * @throws IOException 
     */  
    public static List<String> exportListFromExcel(InputStream is,  
            String extensionName, int sheetNum) throws IOException {  
  
        Workbook workbook = null;  
  
        if (extensionName.toLowerCase().equals(Constant.XLS)) {  
            workbook = new HSSFWorkbook(is);  
        } else if (extensionName.toLowerCase().equals(Constant.XLSX)) {  
            workbook = new XSSFWorkbook(is);  
        }  
  
        return exportListFromExcel(workbook, sheetNum);  
    }  
    /** 
     * 由指定的Sheet导出至List 
     *  
     * @param workbook 
     * @param sheetNum 
     * @return 
     * @throws IOException 
     */  
    private static List<String> exportListFromExcel(Workbook workbook,  
            int sheetNum) {  
  
        Sheet sheet = workbook.getSheetAt(sheetNum);  
  
        // 解析公式结果  
        FormulaEvaluator evaluator = workbook.getCreationHelper()  
                .createFormulaEvaluator();  
  
        List<String> list = new ArrayList<String>();  
  
        int minRowIx = sheet.getFirstRowNum();  
        int maxRowIx = sheet.getLastRowNum();  
        for (int rowIx = minRowIx; rowIx <= maxRowIx; rowIx++) {  
            Row row = sheet.getRow(rowIx);  
            StringBuilder sb = new StringBuilder();  
  
            short minColIx = row.getFirstCellNum();  
            short maxColIx = row.getLastCellNum();  
            for (short colIx = minColIx; colIx <= maxColIx; colIx++) {  
                Cell cell = row.getCell(new Integer(colIx));  
                CellValue cellValue = evaluator.evaluate(cell);  
                if (cellValue == null) {  
                    continue;  
                }  
                // 经过公式解析，最后只存在Boolean、Numeric和String三种数据类型，此外就是Error了  
                // 其余数据类型，根据官方文档，完全可以忽略http://poi.apache.org/spreadsheet/eval.html  
                switch (cellValue.getCellType()) {  
                case Cell.CELL_TYPE_BOOLEAN:  
                    sb.append(SEPARATOR + cellValue.getBooleanValue());  
                    break;  
                case Cell.CELL_TYPE_NUMERIC:  
                    // 这里的日期类型会被转换为数字类型，需要判别后区分处理  
                    if (DateUtil.isCellDateFormatted(cell)) {  
                        sb.append(SEPARATOR + cell.getDateCellValue());  
                    } else {  
                        sb.append(SEPARATOR + cellValue.getNumberValue());  
                    }  
                    break;  
                case Cell.CELL_TYPE_STRING:  
                    sb.append(SEPARATOR + cellValue.getStringValue());  
                    break;  
                case Cell.CELL_TYPE_FORMULA:  
                    break;  
                case Cell.CELL_TYPE_BLANK:  
                    break;  
                case Cell.CELL_TYPE_ERROR:  
                    break;  
                default:  
                    break;  
                }  
            }  
            list.add(sb.toString());  
        }  
        return list;  
    }  
}  
